#!/usr/bin/env python
# coding=utf-8
import time
import pandas as pd
import akshare as ak
import numpy as np
import os
import datetime
import mysql.connector

def getDate(curson,cnx):
    ''' 获取交易日信息并存入数据库date中 '''
    tradeDate = ak.tool_trade_date_hist_sina()
    curson.execute("show tables like 'date'")
    if not curson.fetchone():
        creatDate = '''CREATE TABLE if not EXISTS date (dayNumber INT PRIMARY KEY,tradeDate DATE NOT NULL,UNIQUE (tradeDate));'''
        curson.execute(creatDate)

    for date in tradeDate.itertuples():
        dateSql = '''insert into date values({},"{}")ON DUPLICATE KEY UPDATE dayNumber = dayNumber;'''.format(date[0],date[1])
        curson.execute(dateSql)
        print("更新交易日期 ",date[1])
        # 提交事务
        cnx.commit()
        print("更新交易日日期完成")

def getCode(curson,cnx):
    ''' 获取所有股票代码并存入数据库中  '''
    # 获取沪市股票的所有数据
    dataAllSH = ak.stock_sh_a_spot_em()
    stockCodeSH = dataAllSH[['代码','名称']]

    # 获取深市股票所有数据
    dataAllSZ = ak.stock_sz_a_spot_em()
    stockCodeSZ = dataAllSZ[['代码','名称']]

    # 获取沪深所有股票代码
    stockCodeAll = pd.concat([stockCodeSH, stockCodeSZ]).reset_index(drop=True)

    curson.execute("show tables like 'code'")
    if not curson.fetchone():
        creatCode = ''' CREATE TABLE IF NOT EXISTS code (
        code VARCHAR(10) PRIMARY KEY,
        name VARCHAR(100) NOT NULL,
        market VARCHAR(10) NOT NULL
        ); '''
        curson.execute(creatCode)

    for date in stockCodeAll.itertuples():
        code = date[1]
        if code[0:3] == '688':
            market = '科创板'
        elif code[0] == '3':
            market = '创业板'
        elif code[0:3] in ['600','601','603','605']:
            market = '上海主板'
        elif code[0] == '0':
            market = '深证主板'
        elif code[0] in ['4','8','9']:
            market = '北交所'
        else:
            print(code,' 新的代码类型')

        codeSql = '''insert into code values("{}","{}","{}")ON DUPLICATE KEY UPDATE code = code;'''.format(date[1],date[2],market)
        print("更新代码:" + code)
        curson.execute(codeSql)
        # 提交事务
        cnx.commit()
    print("更新代码完成")

    return stockCodeAll

def getHistory(curson,cnx,stockCodeAll):
    ''' 获取所有股票历史数据 '''
    for data in stockCodeAll.itertuples():
        code = data[1]
        # 总计断板反包天数、连板天数
        topAllDay = 0
        topDay = 0
        curson.execute("show tables like '{}'".format(code))
        if not curson.fetchall():
            creatHistory = '''  CREATE TABLE IF NOT EXISTS `{}` (
            date DATE NOT NULL,
            openPrice DECIMAL(10, 2) NOT NULL,
            closePrice DECIMAL(10, 2) NOT NULL,
            highPrice DECIMAL(10, 2) NOT NULL,
            lowPrice DECIMAL(10, 2) NOT NULL,
            volume INT NOT NULL,
            turnover DECIMAL(20, 2) NOT NULL,
            amplitude DECIMAL(7, 2) NOT NULL,
            changePercent DECIMAL(7, 2) NOT NULL,
            changeAmount DECIMAL(10, 2) NOT NULL,
            turnoverRate DECIMAL(7, 2) NOT NULL,
            isTop INT NOT NULL,
            topAllDayNum INT NOT NULL,
            topDayNum INT NOT NULL,
            PRIMARY KEY (date)
            );  '''.format(code)
            curson.execute(creatHistory)
            print("创建 {} 历史数据".format(code))
            cnx.commit()
        try:
            everyStock = ak.stock_zh_a_hist(symbol=code)
            # 查询表中最新日期，进行更新数据
            curson.execute("select max(`date`) from `{}`;".format(code))
            cutDate = curson.fetchone()
            if cutDate[0] is not None:
                newData = everyStock[everyStock["日期"] > cutDate[0]]
            else:
                newData = everyStock

            print("更新:{},更新行数:{}".format(code,len(newData)))

            for date in newData.itertuples():
                # 判断当天是否涨停(仅主板和创业板)
                if(code[0] != '3'):
                    if(round((date[4] - date[11])*1.1,2) == date[4]):
                        isTop = 1
                    else:
                        isTop = 0
                else:
                    if(round((date[4] - date[11])*1.1,2) <= date[4]):
                        isTop = 1
                    elif(round((date[4] - date[11])*1.2,2) == date[4]):
                        isTop = 2
                    else:
                        isTop = 0

                historySql = ''' insert into `{}` values('{}',{},{},{},{},{},{},{},{},{},{},{},{},{})on duplicate key update `date` = `date`;
                '''.format(code,date[1],date[3],date[4],date[5],date[6],date[7],round(date[8]/10**8,2),date[9],date[10],date[11],date[12],isTop,topAllDay,topDay)
                curson.execute(historySql)
        except:
            print('请等待60s')
            time.sleep(60)

    cnx.commit()
    print("获取全部股票历史数据完成")

def getTopDay(curson,cnx,stockCodeAll):
    ''' 获取几天几板数据 '''
    for data in stockCodeAll.itertuples():
        code = data[1]
        print("几天几板",code)
        getTopSql = ''' select date,isTop,topAllDayNum,topDayNum from `{}`;  '''.format(code)
        curson.execute(getTopSql)
        data = list(zip(*curson.fetchall()))
        if len(data) == 0:
            continue
        date = list(data[0])
        isTop = list(data[1])
        topAllDayNum = list(data[2])
        topDayNum = list(data[3])
        lastIndex = -1
        lastDateIndex = -9
        # 尝试找到最后一次处理的结果
        indices = [i for i, value in enumerate(topAllDayNum) if value != 0]
        lastIndex = indices[-1] if indices else -1
        # 若没有，则通过查找最后一次涨停判断是未处理的数据还是涨停数为0的数据
        if(lastIndex == -1):
            indices = [i for i, value in enumerate(isTop) if value != 0]
            lastIndex = indices[-1] if indices else -1
        # 这个也没有，说明不需要处理,不计算第一天数据
        if(lastIndex == -1):
            continue
        else:
            lastIndex = 1
        # 从之前处理的最后一次开始处理
        for i in range(lastIndex,len(date)):
            tempTop = isTop[i]
            if(tempTop != 0):
                if(lastDateIndex + 4 >= i):
                    topAllDayNum[i] = topAllDayNum[lastDateIndex] + tempTop + i - lastDateIndex - 1
                    topDayNum[i] = topDayNum[lastDateIndex] + tempTop
                    curson.execute("update `{}` set topAllDayNum={},topDayNum={} where date='{}';".format(code,topAllDayNum[i],topDayNum[i],date[i]))
                else:
                    topAllDayNum[i] = topAllDayNum[i - 1] + tempTop
                    topDayNum[i] = topDayNum[i - 1] + tempTop
                    curson.execute("update `{}` set topAllDayNum={},topDayNum={} where date='{}';".format(code,topAllDayNum[i],topDayNum[i],date[i]))
                lastDateIndex = i
                
        cnx.commit()

def getDaily(curson,cnx):
    ''' 获取每一天市场整体情况（通过拆分每只股票的历史获得）  '''
    # 查询交易日信息
    dateSql = ''' SELECT tradeDate from date where tradeDate between '2023-01-03' and '{}'; '''.format((datetime.datetime.now() - datetime.timedelta(hours=15)).strftime('%Y-%m-%d'))
    curson.execute(dateSql)
    date = curson.fetchall()
    # 查询最新股票代码
    codeSql = ''' SELECT code from code ; '''
    curson.execute(codeSql)
    code = curson.fetchall()
    # 按照每天的日期，遍历所有股票提取当天的信息组合成一个文件
    for row in date:
        everyDate = row[0]
        print("整合当天股票信息：",everyDate)
        curson.execute(''' show tables like '{}'  '''.format(everyDate))
        if not curson.fetchall():
            creatDateSql = '''  CREATE TABLE IF NOT EXISTS `{}` (
            stockCode VARCHAR(10) NOT NULL,
            openPrice DECIMAL(10, 2) NOT NULL,
            closePrice DECIMAL(10, 2) NOT NULL,
            highPrice DECIMAL(10, 2) NOT NULL,
            lowPrice DECIMAL(10, 2) NOT NULL,
            openChange DECIMAL(7, 2) NOT NULL,
            closeChange DECIMAL(7, 2) NOT NULL,
            highChange DECIMAL(7, 2) NOT NULL,
            lowChange DECIMAL(7, 2) NOT NULL,
            volume INT NOT NULL,
            turnover DECIMAL(20, 2) NOT NULL,
            amplitude DECIMAL(7, 2) NOT NULL,
            changePercent DECIMAL(7, 2) NOT NULL,
            turnoverRate DECIMAL(5, 2) NOT NULL,
            maxProfit DECIMAL(10, 2) NOT NULL,
            maxLoss DECIMAL(10, 2) NOT NULL,
            PRIMARY KEY (stockCode)
            );  '''.format(everyDate)
            curson.execute(creatDateSql)
            cnx.commit()

            for codeRow in code:
                everyCode = codeRow[0]
                # cyl test
                if everyCode == '002025':
                    continue
                # 判断当天该股票是否上市
                jugeDateSql = ''' SELECT min(date),max(date) from `{}`; '''.format(everyCode)
                curson.execute(jugeDateSql)
                jugeDate = curson.fetchall()[0]
                if jugeDate[0] is not None and jugeDate[0] <= everyDate and jugeDate[1] >= everyDate:
                    priceSql = ''' select * from `{}` where date='{}' '''.format(everyCode,everyDate)
                    curson.execute(priceSql)
                    # 排除停牌的情况
                    temp = curson.fetchall()
                    if temp:
                        price = temp[0]
                    else:
                        continue
                    yesPrice = price[2]/(1 + price[8]/100)
                    historySql = ''' insert into `{}` values('{}',{},{},{},{},{},{},{},{},{},{},{},{},{},{},{})on duplicate key update stockCode = '{}';
                                '''.format(everyDate,everyCode,price[1],price[2],price[3],price[4],round((price[1]/yesPrice - 1)*100,2),round((price[2]/yesPrice - 1)*100,2),
                                  round((price[3]/yesPrice - 1)*100,2),round((price[4]/yesPrice - 1)*100,2),price[5],price[6],price[7],price[8],price[10],
                                  round((price[2] - price[4])/yesPrice*100,2),round((price[3] - price[2])/yesPrice*100,2),everyCode)
                    curson.execute(historySql)
        cnx.commit()
    print("整合历史股票信息完毕")

def poolTool(curson,cnx,poolName,code,date):
    ''' 用于各种池的数据库存储  '''
    curson.execute("show tables like '{}{}'".format(poolName,date))
    if not curson.fetchone():
        creatCode = ''' CREATE TABLE IF NOT EXISTS `{}{}` (code VARCHAR(10) PRIMARY KEY); '''.format(poolName,date)
        curson.execute(creatCode)
        # 如果没有符合条件的股票，则建完表后返回
        if len(code) == 0:
            return
        print(code[0])
        for rowCode in code[0]:
            curson.execute(" insert into `{}{}` values ('{}') ".format(poolName,date,rowCode))
    cnx.commit()

def dealPoolData(curson,cnx):
    ''' 处理每日数据，得到当日的大涨池、大跌池、大肉池、大面池，以及市场总体大涨大跌数据  '''
    # 查询交易日信息
    dateSql = ''' SELECT tradeDate from date where tradeDate between '2023-01-01' and '{}'; '''.format((datetime.datetime.now() - datetime.timedelta(hours=15)).strftime('%Y-%m-%d'))
    curson.execute(dateSql)
    date = curson.fetchall()
    # 查询当天大涨股票、大跌股票、大肉股票(除大涨)、大面股票(除大跌)
    for row in date:
        everyDate = row[0]
        topSql = ''' select * from `{}` where closeChange > 9 '''.format(everyDate)
        downSql = ''' select * from `{}` where closeChange < -9 '''.format(everyDate)
        meatSql = ''' select * from `{}` where maxProfit > 8 and closeChange < 9 '''.format(everyDate)
        noodleSql = ''' select * from `{}` where maxLoss > 8 and closeChange > -9  '''.format(everyDate)
        curson.execute(topSql)
        topData = list(zip(*(curson.fetchall())))
        poolTool(curson,cnx,"topPool",topData,everyDate)

        curson.execute(downSql)
        downData = list(zip(*(curson.fetchall())))
        poolTool(curson,cnx,"downPool",downData,everyDate)

        curson.execute(meatSql)
        meatData = list(zip(*(curson.fetchall())))
        poolTool(curson,cnx,"meatPool",meatData,everyDate)

        curson.execute(noodleSql)
        noodleData = list(zip(*(curson.fetchall())))
        poolTool(curson,cnx,"noodlePool",noodleData,everyDate)
        print("更新 {} 池中数据成功".format(everyDate))

def openAvgTool(curson,cnx,date,code):
    ''' 查一系列代码的开盘价平均值，并返回  '''
    if len(code) == 0:
        return 0;
    if len(code) == 1:
        openSql = ''' select avg(openChange) from `{}` where stockCode in ('{}') '''.format(date,code[0])
    else:
        openSql = ''' select avg(openChange) from `{}` where stockCode in {} '''.format(date,code)
    curson.execute(openSql)
    return round(curson.fetchall()[0][0],2)

def dealLimitData(curson,cnx):
    ''' 得到市场每天总体大涨大跌数据  '''
    # 检查是否存在表dealMarket
    curson.execute(''' show tables  like 'dealMarket'; ''')
    if not curson.fetchall():
        curson.execute('''  CREATE TABLE dealMarket (
                                    date DATE PRIMARY KEY,
                                    topCount INT DEFAULT 0,
                                    downCount INT DEFAULT 0,
                                    meatCount INT DEFAULT 0,
                                    noodleCount INT DEFAULT 0,
                                    topPreminum DECIMAL(10, 2) DEFAULT 0.00,
                                    downDiscount DECIMAL(10, 2) DEFAULT 0.00,
                                    meatPreminum DECIMAL(10, 2) DEFAULT 0.00,
                                    noodleDiscount DECIMAL(10, 2) DEFAULT 0.00,
                                    growthTopCount INT DEFAULT 0,
                                    growthDownCount INT DEFAULT 0,
                                    growthTopPreminum DECIMAL(10, 2) DEFAULT 0.00,
                                    growthDownDiscount DECIMAL(10, 2) DEFAULT 0.00,
                                    topAvgMarketValue DECIMAL(10, 2) DEFAULT 0.00,
                                    downAvgMarketValue DECIMAL(10, 2) DEFAULT 0.00,
                                    topAvgTurnover DECIMAL(10, 2) DEFAULT 0.00,
                                    downAvgTurnover DECIMAL(10, 2) DEFAULT 0.00,
                                    topTire TEXT(60000) NULL
                                     ); ''')

    # 找出未更新数据中最远一天日期
    curson.execute(''' select max(date) from dealMarket; ''')
    maxDateTemp = curson.fetchall()
    if maxDateTemp and maxDateTemp[0][0] is not None:
        maxDate = maxDateTemp[0][0]
    else:
        maxDate = '2023-01-04'
    # 查询交易日信息(错一天)
    dateSql = ''' SELECT tradeDate from date where tradeDate between '{}' and '{}'; '''.format(maxDate,(datetime.datetime.now() - datetime.timedelta(hours=15)).strftime('%Y-%m-%d'))
    curson.execute(dateSql)
    date = curson.fetchall()
    grupDate = list(map(lambda x:x[0],date))
    for rowDate in grupDate:
        dateIndex = grupDate.index(rowDate)
        if dateIndex != 0:
            yesDate = grupDate[dateIndex - 1]
        else:
            yesDate = maxDate
        dailyData = {
            'date':'',
            'topCount':0,
            'downCount':0,
            'meatCount':0,
            'noodleCount':0,
            'topPreminum':0,
            'downDiscount':0,
            'meatPreminum':0,
            'noodleDiscount':0,
            'growthTopCount':0,
            'growthDownCount':0,
            'growthTopPreminum':0,
            'growthDownDiscount':0,
            'topAvgMarketValue':0,
            'downAvgMarketValue':0,
            'topAvgTurnover':0,
            'downAvgTurnover':0,
            'topTire':""
        }
        dailyData['date'] = rowDate
        # 取出当日大涨、大跌、大面、大肉数，及其板块、市值、成交额
        # 大涨
        topSql = '''  select turnover/(turnoverRate + 0.0001)*100 as marketValue,turnover from `topPool{}` as a INNER JOIN  `{}` as b on a.`code` = b.stockCode;
                '''.format(rowDate,rowDate)
        curson.execute(topSql)
        top = list(zip(*curson.fetchall()))
        if len(top) != 0:
            dailyData['topCount'] = len(top[0])
            dailyData['topAvgMarketValue'] = round(np.mean(top[0]),2)
            dailyData['topAvgTurnover'] = round(np.mean(top[1]),2)
        # 大跌
        downSql = '''  select turnover/(turnoverRate + 0.0001)*100 as marketValue,turnover from `downPool{}` as a INNER JOIN  `{}` as b on a.`code` = b.stockCode;
                '''.format(rowDate,rowDate)
        curson.execute(downSql)
        down = list(zip(*curson.fetchall()))
        if len(down) != 0:
            dailyData['downCount'] = len(down[0])
            dailyData['downAvgMarketValue'] = round(np.mean(down[0]),2)
            dailyData['downAvgTurnover'] = round(np.mean(down[1]),2)

        # 大肉
        meatSql = '''  select turnover/(turnoverRate + 0.0001)*100 as marketValue,turnover from `meatPool{}` as a INNER JOIN  `{}` as b on a.`code` = b.stockCode;
                '''.format(rowDate,rowDate)
        curson.execute(meatSql)
        meat = list(zip(*curson.fetchall()))
        if len(meat) != 0:
            dailyData['meatCount'] = len(meat[0])

        # 大面
        noodleSql = '''  select turnover/(turnoverRate + 0.0001)*100 as marketValue,turnover from `noodlePool{}` as a INNER JOIN  `{}` as b on a.`code` = b.stockCode;
                '''.format(rowDate,rowDate)
        curson.execute(noodleSql)
        noodle = list(zip(*curson.fetchall()))
        if len(noodle) != 0:
            dailyData['noodleCount'] = len(noodle[0])
        
        # 创业板大涨数
        growthTopCountSql = '''  select 1 
                                 from `topPool{}` as a
                                 INNER JOIN `code` as b
                                 on a.`code` = b.`code`
                                 where b.market = '创业板';  '''.format(rowDate)
        curson.execute(growthTopCountSql)
        dailyData['growthTopCount'] = len(curson.fetchall())

        # 创业板大跌数
        growthDownCountSql = '''  select 1 
                                 from `downPool{}` as a
                                 INNER JOIN `code` as b
                                 on a.`code` = b.`code`
                                 where b.market = '创业板';  '''.format(rowDate)
        curson.execute(growthDownCountSql)
        dailyData['growthDownCount'] = len(curson.fetchall())

        # 取出昨日大涨、大跌、大面、大肉代码，再取出其今日开盘溢价
        # 大涨溢价
        topCodeSql = ''' select a.`code` 
                         from `topPool{}` as a
                         INNER JOIN `code` as b
                         on a.`code` = b.`code` '''.format(yesDate)
        curson.execute(topCodeSql)
        topCode = list(zip(*curson.fetchall()))
        if len(topCode) != 0:
            dailyData['topPreminum'] = openAvgTool(curson,cnx,rowDate,topCode[0])

        # 大跌折价
        downCodeSql = ''' select a.`code` 
                         from `downPool{}` as a
                         INNER JOIN `code` as b
                         on a.`code` = b.`code` '''.format(yesDate)
        curson.execute(downCodeSql)
        downCode = list(zip(*curson.fetchall()))
        if len(downCode) != 0:
            dailyData['downDiscount'] = openAvgTool(curson,cnx,rowDate,downCode[0])

        # 大肉溢价
        meatCodeSql = ''' select a.`code` 
                         from `meatPool{}` as a
                         INNER JOIN `code` as b
                         on a.`code` = b.`code` '''.format(yesDate)
        curson.execute(meatCodeSql)
        meatCode = list(zip(*curson.fetchall()))
        if len(meatCode) != 0:
            dailyData['meatPreminum'] = openAvgTool(curson,cnx,rowDate,meatCode[0])

        # 大面折价
        noodleCodeSql = ''' select a.`code` 
                         from `noodlePool{}` as a
                         INNER JOIN `code` as b
                         on a.`code` = b.`code` '''.format(yesDate)
        curson.execute(noodleCodeSql)
        noodleCode = list(zip(*curson.fetchall()))
        if len(noodleCode) != 0:
            dailyData['noodleDiscount'] = openAvgTool(curson,cnx,rowDate,noodleCode[0])

        # 创业板大涨溢价
        growthTopCodeSql = ''' select a.`code` 
                         from `topPool{}` as a
                         INNER JOIN `code` as b
                         on a.`code` = b.`code` 
                         where b.market="创业板" '''.format(yesDate)
        curson.execute(growthTopCodeSql)
        growthTopCode = list(zip(*curson.fetchall()))
        if len(growthTopCode) != 0:
            dailyData['growthTopPreminum'] = openAvgTool(curson,cnx,rowDate,growthTopCode[0])

        # 创业板大跌折价
        growthDownCodeSql = ''' select a.`code` 
                         from `downPool{}` as a
                         INNER JOIN `code` as b
                         on a.`code` = b.`code` 
                         where b.market="创业板" '''.format(yesDate)
        curson.execute(growthDownCodeSql)
        growthDownCode = list(zip(*curson.fetchall()))
        if len(growthDownCode) != 0:
            dailyData['growthDownDiscount'] = openAvgTool(curson,cnx,rowDate,growthDownCode[0])
        
        # 涨停梯队元组（代码、天数、连板数）
        curson.execute(''' select a.code,name from `topPool{}` as a inner join `code` as b on a.`code` = b.`code`;'''.format(rowDate))
        topPoolTire = list(zip(*curson.fetchall()))
        if topPoolTire[0] is None:
            continue
        topTire = [[()] for _ in range(30)]
        for i in range(len(topPoolTire[0])):
            topTireSql = ''' select isTop,topAllDayNum,topDayNum from `{}` where date='{}' ; '''.format(topPoolTire[0][i],rowDate)
            curson.execute(topTireSql)
            temp = curson.fetchall()[0]
            if(temp[0] != 1):
                continue
            topTire[temp[2]].append((topPoolTire[1][i],temp[1],temp[2]))
        dailyData['topTire'] = str(topTire)


        dealMarketSql = ''' insert into dealMarket values ('{}',{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},"{}") ON DUPLICATE KEY UPDATE topCount={};  '''.format(dailyData['date'],dailyData['topCount'],dailyData['downCount']
                    ,dailyData['meatCount'],dailyData['noodleCount'],dailyData['topPreminum'],dailyData['downDiscount'],dailyData['meatPreminum'],dailyData['noodleDiscount']
                    ,dailyData['growthTopCount'],dailyData['growthDownCount'],dailyData['growthTopPreminum'],dailyData['growthDownDiscount'],dailyData['topAvgMarketValue']
                    ,dailyData['downAvgMarketValue'],dailyData['topAvgTurnover'],dailyData['downAvgTurnover'],dailyData['topTire'],dailyData['topCount'])
        curson.execute(dealMarketSql)
        cnx.commit()
        print("更新每日市场总体大涨大跌数据",dailyData['date'])

if __name__ == '__main__':
    # 配置数据库基本信息
    config = {
        'user': 'cyl',
        'password': '5h2oh2SO4!!',
        'port':1654,
        'host': 'localhost',
        'database': 'marketCondition',
        'raise_on_warnings': True
    }
    # 连接数据库
    cnx = mysql.connector.connect(**config)
    # 创建游标对象
    curson = cnx.cursor()

    # 获取交易日信息并存入数据库中
    getDate(curson,cnx)

    # 获取所有股票代码并更新到数据库中
    stockCodeAll = getCode(curson,cnx)

    # 获取所有股票历史数据(未退市)
    getHistory(curson,cnx,stockCodeAll)

    # 获取所有股票几天几板数据
    getTopDay(curson,cnx,stockCodeAll)

    # 从股票历史数据中获取当天股票的市场总览
    getDaily(curson,cnx)

    # 处理每日数据，得到当日的大涨池、大跌池
    dealPoolData(curson,cnx)

    # 得到市场总体大涨大跌数据
    dealLimitData(curson,cnx)

    # 关闭游标和数据库
    curson.close()
    cnx.close()



